package com.vclee.fast;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;

import java.io.*;
import java.lang.reflect.Field;
import java.nio.charset.StandardCharsets;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import static com.alibaba.fastjson.serializer.SerializerFeature.DisableCircularReferenceDetect;
import static com.alibaba.fastjson.serializer.SerializerFeature.WriteDateUseDateFormat;

/**
 * Author: sir.li
 * email:  lesli2@qq.com
 * Date:   2020/8/6
 */
public class DbCriteria {
    private static final Logger logger = LoggerFactory.getLogger(DbCriteria.class);
    private static final List<String> list = Arrays.asList("String", "Integer", "Long", "Double", "BigDecimal", "BigInteger");
    private static final JdbcTemplate jdbcTemplate = createJdbcTemplate();

    public static <T> List<T> queryList(String preSql, Class<T> clazz, Object... params) {
        if (list.contains(clazz.getSimpleName())) {
            return jdbcTemplate.queryForList(preSql, clazz, params);
        }
        return jdbcTemplate.query(preSql, new BeanPropertyRowMapper<>(clazz), params);
    }

    public static List<JSONObject> queryList(String preSql, Object... params) {
        List<JSONObject> rList = new ArrayList<>();
        List<Map<String, Object>> list = jdbcTemplate.queryForList(preSql, params);
        if (!list.isEmpty()) {
            for (Map<String, Object> map : list) {
                JSONObject rMap = new JSONObject();
                map.forEach((key, value) -> rMap.put(underlineToCamel(key), value));
                rList.add(rMap);
            }
        }
        return rList;
    }

    public static <T> T queryObject(String preSql, Class<T> clazz, Object... params) {
        try {
            if (list.contains(clazz.getSimpleName())) {
                return jdbcTemplate.queryForObject(preSql, clazz, params);
            }
            return jdbcTemplate.queryForObject(preSql, new BeanPropertyRowMapper<>(clazz), params);
        } catch (IncorrectResultSizeDataAccessException e) {
//            logger.error("查询单个数据sql错误,原因为：{}", e.getMessage(),e);
            return null;
        }
    }

    public static <T> T queryById(Object onlyKey, Class<T> clazz) {
        return queryObject("select * from " + getTableName(clazz) + " where id=" + preValue(onlyKey), clazz);
    }

    public static <T> T queryById(Object onlyKey, String tableName, Class<T> clazz) {
        return queryObject("select * from " + tableName + " where id=" + preValue(onlyKey), clazz);
    }

    public static JSONObject queryForJsonObject(String preSql, Object... params) {
        try {
            Map<String, Object> map = jdbcTemplate.queryForMap(preSql, params);
            Map<String, Object> rMap = new HashMap<>();
            map.forEach((key, value) -> rMap.put(underlineToCamel(key), value));
            return JSONObject.parseObject(JSONObject.toJSONString(rMap));
        } catch (IncorrectResultSizeDataAccessException e) {
//            logger.error("查询单个数据sql错误,原因为：{}", e.getMessage(), e);
            return null;
        }
    }

    public static int update(String preSql, Object... params) {
        return jdbcTemplate.update(preSql, params);
    }

    public static int updateById(Object obj) {
        return updateById(obj, getTableName(obj.getClass()));
    }

    public static int updateById(Object obj, String tableName) {
        JSONObject json = JSONObject.parseObject(JSON.toJSONString(obj, DisableCircularReferenceDetect, WriteDateUseDateFormat));
        StringBuilder preSql = new StringBuilder("update " + tableName + " set ");
        List<Object> params = new ArrayList<>();
        json.forEach((key, value) -> {
            if (!key.equals("id")) {
                preSql.append("`").append(camelToUnderline(key)).append("`").append("=?,");
                params.add(value);
            }
        });
        preSql.deleteCharAt(preSql.length() - 1);
        preSql.append(" where id = ?");
        params.add(json.get("id"));
        return jdbcTemplate.update(preSql.toString(), params.toArray());
    }

    /**
     * 批量更新
     */
    public static int[] batchUpdate(List<Object[]> batchArgs, String sql) {
        return jdbcTemplate.batchUpdate(sql, batchArgs);
    }

    /**
     * 非自增插入
     *
     * @param object    对象字段
     * @param tableName 表名称
     * @return 插入数量
     */
    public static int insertNx(Object object, String tableName) {
        return jdbcTemplate.update(createSingleTableInsertSqlByEntity(object, tableName));
    }

    public static int insertNx(Object object) {
        return insertNx(object, getTableName(object.getClass()));
    }

    public static int insert(Object object) {
        return insert(object, getTableName(object.getClass()));
    }

    /**
     * 自增插入(自动设置插入对象的主键)
     *
     * @param object    对象字段
     * @param tableName 表名称
     * @return 插入数量
     */
    public static int insert(Object object, String tableName) {
        try {
            String preSql = createSingleTableInsertSqlByEntity(object, tableName);
            GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
            int addNum = jdbcTemplate.update(connection -> connection.prepareStatement(preSql, PreparedStatement.RETURN_GENERATED_KEYS), keyHolder);
            Class<?> clazz = object.getClass();
            Field name = clazz.getDeclaredField("id");
            name.setAccessible(true);
            if (name.getGenericType().getTypeName().contains("java.lang.Long")) {
                name.set(object, Objects.requireNonNull(keyHolder.getKey()).longValue());
            } else {
                name.set(object, Objects.requireNonNull(keyHolder.getKey()).intValue());
            }
            return addNum;
        } catch (IllegalAccessException | NoSuchFieldException e) {
            logger.error("pojo对象反射Id字段失败:" + e.getMessage(), e);
            return 0;
        }
    }

    public static int insert(String preSql, Object... params) {
        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(preSql, PreparedStatement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < params.length; i++) {
                Object param = params[i];
                ps.setObject(i + 1, param);
            }
            return ps;
        }, keyHolder);
        return Objects.requireNonNull(keyHolder.getKey()).intValue();
    }

    public static int delete(String preSql, Object... params) {
        return jdbcTemplate.update(preSql, params);
    }

    @SuppressWarnings("all")
    public static int deleteById(String tableName, Object id) {
        return jdbcTemplate.update("delete from " + tableName + " where id = ?", id);
    }

    /**
     * 根据对象生成单表插入sql语句(对象字段可以直接插入表)
     *
     * @param obj       表字段对象
     * @param tableName 插入表名
     * @return preSql 预检查sql
     */
    private static String createSingleTableInsertSqlByEntity(Object obj, String tableName) {
        JSONObject json = JSONObject.parseObject(JSON.toJSONString(obj, DisableCircularReferenceDetect, WriteDateUseDateFormat));
        StringBuilder preSql = new StringBuilder("insert into " + tableName);
        StringBuilder keys = new StringBuilder(" (");
        StringBuilder values = new StringBuilder("(");
        json.forEach((key, value) -> {
            keys.append("`").append(camelToUnderline(key)).append("`").append(",");
            values.append(preValue(value)).append(",");
        });
        keys.deleteCharAt(keys.length() - 1);
        values.deleteCharAt(values.length() - 1);
        keys.append(") values ");
        values.append(");");
        preSql.append(keys).append(values);
        return preSql.toString();
    }

    private static Object preValue(Object value) {
        if (value instanceof String) {
            return "'" + value + "'";
        } else if (value instanceof Number) {
            return value;
        } else if (value instanceof Date) {
            return Timestamp.valueOf(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(value));
        } else {
            return "'" + JSONObject.toJSONString(value) + "'";
        }
    }

    private static String camelToUnderline(String filedName) {
        int len = filedName.length();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c = filedName.charAt(i);
            if (Character.isUpperCase(c)) {
                sb.append("_");
                sb.append(Character.toLowerCase(c));
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }


    static JdbcTemplate createJdbcTemplate() {
        logger.warn("applicationContext未初始化启用mysql默认配置===>");
        long startTime = System.currentTimeMillis();
        HikariConfig config = getHikariConfig();
        HikariDataSource source = new HikariDataSource(config);
        JdbcTemplate template = new JdbcTemplate(source);
        logger.info("database connection pool initialization completed,use database Driver:"
                + config.getDriverClassName() + ",consumed startup time:" + (System.currentTimeMillis() - startTime) + "ms");
        return template;
    }

    private static HikariConfig getHikariConfig() {
        HikariConfig config = new HikariConfig();
        config.setMinimumIdle(200);
        config.setMaximumPoolSize(500);
        config.setMaxLifetime(60000);
        config.setIdleTimeout(30000);
        config.setConnectionTestQuery("SELECT 1");
        config.setJdbcUrl("jdbc:mysql://10.0.5.14:3306/jyag?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8");
        config.setUsername("root");
        config.setPassword("Ange@my#0717");
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        return config;
    }

    private static void executeInitSQL(JdbcTemplate template) {
        ClassLoader loader = DbCriteria.class.getClassLoader();
        InputStream schemaStream = Objects.requireNonNull(loader.getResourceAsStream("db/schema.sql"));
        BufferedReader schemaReader = new BufferedReader(new InputStreamReader(schemaStream));
        String preSql = schemaReader.lines().collect(Collectors.joining(System.lineSeparator()));
        logger.info("输出sql语句:\n{}", preSql);
        template.execute(preSql);
    }

    public static void asyncFlushDto(String url) {
        new Thread(() -> {
            long startTime = System.currentTimeMillis();
            // DB2执行SQL刷新
            String preSql;
            if (url.contains("mysql")) {
                String[] arr = url.split("\\?")[0].split("/");
                String database = arr[arr.length - 1];
                preSql = "select table_name,column_name,column_comment remarks,data_type " +
                        "type_name from information_schema.columns where table_schema = '" + database + "'" + " order by table_name asc, ordinal_position asc";
            } else {
                String[] arr = url.split(";")[0].split("/");
                String database = arr[arr.length - 1];
                preSql = "select a.table_name,b.column_name,b.remarks,b.type_name from " +
                        "information_schema.tables a left join information_schema.columns b on a.table_name = b.table_name " +
                        "where a.table_type = 'TABLE' and a.table_schema = 'PUBLIC' and  a.table_catalog = '" + database + "'";
            }
            String p = DbCriteria.class.getPackage().getName();
            String p2 = p.substring(0, p.lastIndexOf("."));
            String path2 = p2.replace(".", File.separator);
            String path = System.getProperty("user.dir")
                    + File.separator + "src" + File.separator + "main" + File.separator + "java"
                    + File.separator + path2 + File.separator + "model" + File.separator + "bo" + File.separator;
            DbCriteria.queryList(preSql).stream().collect(Collectors.groupingBy(map -> map.get("tableName"))).forEach((key, value) -> {
                String str = underlineToCamel(key.toString());
                String clazz = str.substring(0, 1).toUpperCase() + str.substring(1);
                StringBuilder builder = new StringBuilder("package " + p2 + "." + "model.bo;\n\n");
                int site = builder.length();
                builder.append("import lombok.Data;\n");
                builder.append("\n@Data\n");
                builder.append("public class ").append(clazz).append(" {\n");
                for (Map<String, Object> field : value) {
                    StringBuilder buffer = new StringBuilder("\tprivate");
                    String type = field.get("typeName").toString().toUpperCase();
                    switch (type) {
                        case "BIGINT":
                            buffer.append(" Long");
                            break;
                        case "INTEGER":
                        case "TINYINT":
                        case "SMALLINT":
                        case "INT":
                            buffer.append(" Integer");
                            break;
                        case "TIMESTAMP":
                            if (builder.indexOf("import java.util.Date;") < 0) {
                                builder.insert(site, "import java.util.Date;\n");
                            }
                            buffer.append(" Date");
                            break;
                        case "DECIMAL":
                            if (builder.indexOf(" import java.math.BigDecimal;") < 0) {
                                builder.insert(site, " import java.math.BigDecimal;\n");
                            }
                            buffer.append(" BigDecimal");
                            break;
                        case "TEXT":
                        case "VARCHAR":
                            buffer.append(" String");
                            break;
                        default:
                            logger.info("未知filed:" + JSONObject.toJSONString(field));
                            break;
                    }
                    String name = underlineToCamel(field.get("columnName").toString());
                    buffer.append(" ").append(name).append(";");
                    String comment = field.get("remarks").toString();
                    buffer.append("// ").append(comment).append("\n");
                    builder.append(buffer);
                }
                builder.append("}\n");
                try {
                    RandomAccessFile file = new RandomAccessFile(path + clazz + ".java", "rws");
                    file.setLength(0);
                    file.write(builder.toString().getBytes(StandardCharsets.UTF_8));
                    file.close();
                } catch (Exception e) {
                    logger.info("失败的创建了model对象:" + clazz);
                }
            });
            logger.info("CACHE与DB初始化完成,耗时" + (System.currentTimeMillis() - startTime));
        }).start();
    }


    private static String getTableName(Class<?> clazz) {
        String typeName = clazz.getSimpleName();
        if (typeName.contains(".")) {
            String preName = typeName.substring(typeName.lastIndexOf(".") + 1);
            String first = preName.substring(0, 1);
            return "`" + camelToUnderline(preName.replaceFirst(first, first.toLowerCase())) + "`";
        } else if (!typeName.contains("(")) {
            String first = typeName.substring(0, 1);
            return "`" + camelToUnderline(typeName.replaceFirst(first, first.toLowerCase())) + "`";
        } else {
            // 解决匿名内部类无法获取类对应的包名
            typeName = clazz.toString();
            String preName = typeName.substring(0, typeName.lastIndexOf("("));
            String first = preName.substring(0, 1);
            return "`" + camelToUnderline(preName.replaceFirst(first, first.toLowerCase())) + "`";
        }
    }

    private static String underlineToCamel(String str) {
        str = str.toLowerCase();
        StringBuffer sb = new StringBuffer();
        Matcher matcher = Pattern.compile("_(\\w)").matcher(str);
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }
}



